

'''
-- sku表
CREATE TABLE IF NOT EXISTS sku (
    sku_id SERIAL PRIMARY KEY,
    spu_id INT NOT NULL,
    sku_price DECIMAL(10,2) DEFAULT 0.00,
    sku_img JSON,
    brand_id INT DEFAULT 1,
    sku_attributes JSON NOT NULL,
    unit VARCHAR(10) NOT NULL, -- 单位
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (spu_id) REFERENCES spu(spu_id)
);'''
class SkuTableMethods:

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_sku(self, spu_id, sku_price, sku_img, brand_id, sku_attributes, unit):


        sql = "INSERT INTO sku (spu_id, sku_price, sku_img, brand_id, sku_attributes, unit) VALUES (%s, %s, %s, %s, %s, %s) RETURNING sku_id"

        self.cursor.execute(sql, (spu_id, sku_price, sku_img, brand_id, sku_attributes, unit))

        self.conn.commit()

        return self.cursor.fetchone()["sku_id"]
    

    def get_sku_by_id(self, sku_id):

        sql = "SELECT * FROM sku WHERE sku_id = %s"

        self.cursor.execute(sql, (sku_id,))

        result = self.cursor.fetchone()


        return result
    
    def get_sku_by_spu_id(self, spu_id):

        sql = "SELECT * FROM sku WHERE spu_id = %s"

        self.cursor.execute(sql, (spu_id,))

        result = self.cursor.fetchall()

        return result
    
    def update_sku(self, sku_id, sku_price, sku_img, brand_id, sku_attributes, unit):
        '''\
        更新sku表
        :param sku_id: 商品id 例如:1
        :param sku_price: 商品价格 例如:199.00
        :param sku_img: 商品图片 格式为json 例如：["http://img.com/1.jpg", "http://img.com/2.jpg"]
        :param brand_id: 品牌id
        :param sku_attributes: 商品属性 sku_attributes格式为json 例如：{"颜色": "红色", "尺寸": "M"}
        :param unit: 商品单位 例如：件、盒
        :return: True
        '''
        sql = "UPDATE sku SET sku_price = %s, sku_img = %s, brand_id = %s, \
            sku_attributes = %s, unit = %s WHERE sku_id = %s"

        self.cursor.execute(sql, (sku_price, sku_img, brand_id, sku_attributes, unit, sku_id))

        self.conn.commit()

        return True
    
    

    def delete_sku(self, sku_id):
        '''
        删除sku表
        :param sku_id: 商品id 例如:1
        :return: True or False
        '''

        sql = "DELETE FROM sku WHERE sku_id = %s"

        try:

            self.cursor.execute(sql, (sku_id,))

        except Exception as e:

            print(e)

            return False

        self.conn.commit()

        return True
    
    # def select_sku_by_filter_criteria(self, filter_criteria):
    #     '''
    #     根据过滤条件查询sku
    #     :param filter_criteria: 过滤条件 例如：{"颜色": "红色", "尺寸": "M"}
    #     :return: sku列表
    #     '''
    #     sql = "SELECT * FROM sku WHERE sku_attributes @> %s"

    #     self.cursor.execute(sql, (filter_criteria,))


    #     result = self.cursor.fetchall()

    #     return result

    


